SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Create View template
-- =============================================



CREATE VIEW [dbo].[LotLevelFormulas] AS

SELECT  

		CASE WHEN CAST(llRaw.[Account Number] AS  INT) = 0 THEN llRaw.[Client Fund] + '-' + CAST(CAST(llRaw.[Lot Number] AS INT) AS NVARCHAR(50)) +'-' +LEFT(llRaw.CUSIP,2)  ELSE llRaw.[Client Fund] +'-' + CAST(CAST(llRaw.[Account Number] AS INT)  AS NVARCHAR(20))
END SSID,
		llRaw.[Security Description] ,
        llRaw.CUSIP ,
        llRaw.[Lot Number] ,
        llRaw.[Trade Date] ,
        llRaw.[Actual Settlement Date] ,
        llRaw.[Asset Class] ,
        llRaw.[Base Market Value] ,
        llRaw.[Base Original Cost] ,
        llRaw.[Shares/Par Value] ,
        llRaw.[Base Unrealized Gain/Loss] ,
        llRaw.[Amortized Cost - Base] ,
        llRaw.[Amortized Cost - Local] ,
        llRaw.[Client Fund] ,
        llRaw.[Account Number] ,
        llRaw.[Variable Rate] ,
        llRaw.[Trade ID] ,
        llRaw.[Moody's Quality Rating] ,
        llRaw.[Maturity Date] ,
        llRaw.[Interest Rate] ,
        llRaw.[IM ID] ,
        llRaw.[Alternate Lot Number] ,
        llRaw.[Issued at OID] ,
        llRaw.[Dummy Cusip Indicator] ,
        llRaw.[ISIN Number] ,
        llRaw.[Report Date] ,
        llRaw.[Post Date] ,
        llRaw.[Cash Date] FROM  dbo.LotLevelHoldingsRaw llRaw
GO
